package com.hllxd.hism.star.basic.service.dao.ck;


import com.hllxd.hism.star.basic.service.config.clickhouse.CkClient2;
import com.hllxd.hism.star.basic.service.model.TraceImc;
import com.hllxd.hism.star.basic.service.model.UserTrace;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @author xiyong.lxy
 * @date 2018-12-21 8:53
 */
@Slf4j
@Service("userTraceMapper")
public class UserTraceMapperImpl implements UserTraceMapper {

    @Autowired
    private CkClient2 ckClient2;


    private String getQuerySql(String ts1, String ts2, String userId,Boolean hasExp) {

        // exp
        String expSql = "    select toString(toYYYYMMDDhhmmss(e_time)) as report_time," +
                "        business_line as biz," +
                "        platform,pid, slot, ad_id, ad_disptype, " +
                "        company_name, tjfrom, 'hdp_lbg_supin_dwd_zp_realexposure' as table_name" +
                "    from hdp_lbg_supin_clickhousedb.hdp_lbg_supin_dwd_zp_realexposure" +
                "    where toYYYYMMDDhhmmss(e_time)>=toYYYYMMDDhhmmss(toDateTime('"+ts1+"'))" +
                "        and toYYYYMMDDhhmmss(e_time)<=toYYYYMMDDhhmmss(toDateTime('"+ts2+"'))" +
                "        and userid='" + userId + "'";

        String sql = "select report_time,biz," +
                "    platform,pid, slot, ad_id, ad_disptype, " +
                "    company_name, tjfrom, table_name " +
                "from ( " +
                (hasExp?(expSql+" union all "):"") +

                // ganji click
                "    select toString(toYYYYMMDDhhmmss(e_time)) as report_time," +
                "        business_line as biz," +
                "        platform,pid, slot, info_id as ad_id, info_type as ad_disptype, " +
                "        info_enterprise_name as company_name, tjfrom, 'hdp_lbg_supin_dwd_zp_ganji_zzdetail' as table_name" +
                "    from hdp_lbg_supin_clickhousedb.hdp_lbg_supin_dwd_zp_ganji_zzdetail" +
                "    where toYYYYMMDDhhmmss(e_time)>=toYYYYMMDDhhmmss(toDateTime('"+ts1+"'))" +
                "        and toYYYYMMDDhhmmss(e_time)<=toYYYYMMDDhhmmss(toDateTime('"+ts2+"'))" +
                "        and userid='" + userId + "'" +

                // 58 click
                "    union all" +
                "    select toString(toYYYYMMDDhhmmss(e_time)) as report_time," +
                "        business_line as biz," +
                "        platform,pid, slot, info_id as ad_id, info_type as ad_disptype, " +
                "        info_enterprise_name as company_name, tjfrom, 'hdp_lbg_supin_dwd_zp_58_zzdetail' as table_name" +
                "    from hdp_lbg_supin_clickhousedb.hdp_lbg_supin_dwd_zp_58_zzdetail" +
                "    where toYYYYMMDDhhmmss(e_time)>=toYYYYMMDDhhmmss(toDateTime('"+ts1+"'))" +
                "        and toYYYYMMDDhhmmss(e_time)<=toYYYYMMDDhhmmss(toDateTime('"+ts2+"'))" +
                "        and userid='" + userId + "'" +

                // delivery
                "    union all" +
                "    select toString(toYYYYMMDDhhmmss(e_time)) as report_time," +
                "        business_line as biz," +
                "        platform,pid, slot, ad_id, ad_disptype, " +
                "        company_name, tjfrom, 'hdp_lbg_supin_dwd_zp_resumedelivery' as table_name" +
                "    from hdp_lbg_supin_clickhousedb.hdp_lbg_supin_dwd_zp_resumedelivery" +
                "    where toYYYYMMDDhhmmss(e_time)>=toYYYYMMDDhhmmss(toDateTime('"+ts1+"'))" +
                "        and toYYYYMMDDhhmmss(e_time)<=toYYYYMMDDhhmmss(toDateTime('"+ts2+"'))" +
                "        and userid='" + userId + "'" +

                // tel
                "    union all" +
                "    select toString(toYYYYMMDDhhmmss(e_time)) as report_time," +
                "        business_line as biz," +
                "        platform,pid, slot, ad_id, ad_disptype, " +
                "        company_name,tjfrom,'hdp_lbg_supin_dwd_axbbill_info' as table_name" +
                "    from hdp_lbg_supin_clickhousedb.hdp_lbg_supin_dwd_axbbill_info" +
                "    where toYYYYMMDDhhmmss(e_time)>=toYYYYMMDDhhmmss(toDateTime('"+ts1+"'))" +
                "        and toYYYYMMDDhhmmss(e_time)<=toYYYYMMDDhhmmss(toDateTime('"+ts2+"'))" +
                "        and userid='" + userId + "'" +

                // im
                "    union all" +
                "    select toString(toYYYYMMDDhhmmss(opentime)) as report_time," +
                "        business_line as biz," +
                "        platform,'' as pid, slot, infoid as ad_id, ad_disptype, " +
                "        company_name, tjfrom, 'hdp_lbg_supin_dwd_zp_im_open' as table_name" +
                "    from hdp_lbg_supin_clickhousedb.hdp_lbg_supin_dwd_zp_im_open" +
                "    where toYYYYMMDDhhmmss(opentime)>=toYYYYMMDDhhmmss(toDateTime('"+ts1+"'))" +
                "        and toYYYYMMDDhhmmss(opentime)<=toYYYYMMDDhhmmss(toDateTime('"+ts2+"'))" +
                "        and cuid='" + userId + "'" +
                ") order by report_time desc";
        log.info("query sql: {}", sql);
        return sql;
    }
    @Override
    public List<UserTrace> findUserTrace(String ts1, String ts2, String userId, Boolean hasExp) {
        String querySql = getQuerySql(ts1, ts2, userId, hasExp);
        return queryData(querySql);
    }

    private String getQueryImc(String ts1, String ts2, String infoId,Boolean hasExp) {

        String expSql = "    select toString(toYYYYMMDDhhmmss(e_time)) as report_time," +
                "        business_line as biz," +
                "        platform,pid, slot, userid as user_id, ad_disptype, " +
                "        sloc1 as disp_local1,tjfrom,'hdp_lbg_supin_dwd_zp_realexposure' as table_name" +
                "    from hdp_lbg_supin_clickhousedb.hdp_lbg_supin_dwd_zp_realexposure" +
                "    where toYYYYMMDDhhmmss(e_time)>=toYYYYMMDDhhmmss(toDateTime('"+ts1+"'))" +
                "        and toYYYYMMDDhhmmss(e_time)<=toYYYYMMDDhhmmss(toDateTime('"+ts2+"'))" +
                "        and ad_id='" + infoId + "'";


        String sql = "select report_time,biz," +
                "    platform, pid, slot, user_id, ad_disptype, " +
                "    disp_local1, tjfrom, table_name " +
                "from ( " +

                (hasExp?(expSql+" union all "):"") +

                // ganji click
                "    select toString(toYYYYMMDDhhmmss(e_time)) as report_time," +
                "        business_line as biz," +
                "        platform,pid, slot, userid as user_id, info_type as ad_disptype, " +
                "        disp_local1,tjfrom,'hdp_lbg_supin_dwd_zp_ganji_zzdetail' as table_name" +
                "    from hdp_lbg_supin_clickhousedb.hdp_lbg_supin_dwd_zp_ganji_zzdetail" +
                "    where toYYYYMMDDhhmmss(e_time)>=toYYYYMMDDhhmmss(toDateTime('"+ts1+"'))" +
                "        and toYYYYMMDDhhmmss(e_time)<=toYYYYMMDDhhmmss(toDateTime('"+ts2+"'))" +
                "        and info_id='" + infoId + "'" +
                // 58 click
                "    union all" +
                "    select toString(toYYYYMMDDhhmmss(e_time)) as report_time," +
                "        business_line as biz," +
                "        platform, pid, slot, userid as user_id, info_type as ad_disptype, " +
                "        disp_local1,tjfrom,'hdp_lbg_supin_dwd_zp_58_zzdetail' as table_name" +
                "    from hdp_lbg_supin_clickhousedb.hdp_lbg_supin_dwd_zp_58_zzdetail" +
                "    where toYYYYMMDDhhmmss(e_time)>=toYYYYMMDDhhmmss(toDateTime('"+ts1+"'))" +
                "        and toYYYYMMDDhhmmss(e_time)<=toYYYYMMDDhhmmss(toDateTime('"+ts2+"'))" +
                "        and info_id='" + infoId + "'" +

                // delivery
                "    union all" +
                "    select toString(toYYYYMMDDhhmmss(e_time)) as report_time," +
                "        business_line as biz," +
                "        platform,pid, slot, userid as user_id, ad_disptype, " +
                "        sloc1 as disp_local1,tjfrom,'hdp_lbg_supin_dwd_zp_resumedelivery' as table_name" +
                "    from hdp_lbg_supin_clickhousedb.hdp_lbg_supin_dwd_zp_resumedelivery" +
                "    where toYYYYMMDDhhmmss(e_time)>=toYYYYMMDDhhmmss(toDateTime('"+ts1+"'))" +
                "        and toYYYYMMDDhhmmss(e_time)<=toYYYYMMDDhhmmss(toDateTime('"+ts2+"'))" +
                "        and ad_id='" + infoId + "'" +

                // tel
                "    union all" +
                "    select toString(toYYYYMMDDhhmmss(e_time)) as report_time," +
                "        business_line as biz," +
                "        platform,pid, slot, userid as user_id, ad_disptype, " +
                "        local1 as disp_local1,tjfrom,'hdp_lbg_supin_dwd_axbbill_info' as table_name" +
                "    from hdp_lbg_supin_clickhousedb.hdp_lbg_supin_dwd_axbbill_info" +
                "    where toYYYYMMDDhhmmss(e_time)>=toYYYYMMDDhhmmss(toDateTime('"+ts1+"'))" +
                "        and toYYYYMMDDhhmmss(e_time)<=toYYYYMMDDhhmmss(toDateTime('"+ts2+"'))" +
                "        and ad_id='" + infoId + "'" +

                // im
                "    union all" +
                "    select toString(toYYYYMMDDhhmmss(opentime)) as report_time," +
                "        business_line as biz," +
                "        platform,'' as pid, slot, cuid as user_id, ad_disptype, " +
                "        infolocalid1 as disp_local1,tjfrom,'hdp_lbg_supin_dwd_zp_im_open' as table_name" +
                "    from hdp_lbg_supin_clickhousedb.hdp_lbg_supin_dwd_zp_im_open" +
                "    where toYYYYMMDDhhmmss(opentime)>=toYYYYMMDDhhmmss(toDateTime('"+ts1+"'))" +
                "        and toYYYYMMDDhhmmss(opentime)<=toYYYYMMDDhhmmss(toDateTime('"+ts2+"'))" +
                "        and infoid='" + infoId + "'" +
                ") order by report_time desc";

        log.info("query sql: {}", sql);
        return sql;
    }



    /**
     * 扩展通用处理
     * @param sql 查询SQL
     * @return 返回通用的序列
     */
    public List<UserTrace> queryData(String sql) {
        log.info("query sql: {}.",sql);
        try {
            List<UserTrace> entityList = new ArrayList<>();
            ResultSet resultSet = CkClient2.query(sql);
            while (resultSet.next()) {
                UserTrace entity = new UserTrace();

                entity.reportTime = resultSet.getString("report_time");
                entity.biz = resultSet.getString("biz");
                entity.platform = resultSet.getString("platform");
                entity.slot = resultSet.getString("slot");
                entity.adId = resultSet.getLong("ad_id");
                entity.adDispType = resultSet.getString("ad_disptype");
                entity.companyName = resultSet.getString("company_name");
                entity.tjfrom = resultSet.getString("tjfrom");
                entity.tableName = resultSet.getString("table_name");

                entityList.add(entity);
            }
            log.info("row count: {}", entityList.size());
            return entityList;
        }catch (Exception e) {
            log.error("Exception: {}.",e.toString());
        }
        return null;
    }

    /**
     * 扩展通用处理
     * @param sql 查询SQL
     * @return 返回通用的序列
     */
    public List<TraceImc> queryDataImc(String sql) {
        try {
            List<TraceImc> entityList = new ArrayList<>();
            ResultSet resultSet = CkClient2.query(sql);
            while (resultSet.next()) {
                TraceImc entity = new TraceImc();

                entity.reportTime = resultSet.getString("report_time");
                entity.biz = resultSet.getString("biz");
                entity.platform = resultSet.getString("platform");
                entity.slot = resultSet.getString("slot");
                entity.userId = resultSet.getString("user_id");
                entity.adDispType = resultSet.getString("ad_disptype");
                entity.dispLocal1 = resultSet.getString("disp_local1");
                entity.tjfrom = resultSet.getString("tjfrom");
                entity.tableName = resultSet.getString("table_name");

                entityList.add(entity);
            }
            log.info("row count: {}", entityList.size());
            return entityList;
        }catch (Exception e) {
            log.error("Exception: {}.",e.toString());
        }
        return null;
    }

    @Override
    public List<TraceImc> findTraceImc(String ts1, String ts2, String infoId, Boolean hasExp) {
        String querySql = getQueryImc(ts1, ts2, infoId, hasExp);
        return queryDataImc(querySql);
    }
}
